from typing import List

from openpyxl import Workbook
from sqlalchemy.orm import Query

from apps.a_common.constants import PROJECT_STATUS_MAP, SKILL_MAP
from utils.time import to_formatted_time

from apps.projects.models import Project, ProjectUser


def search_project_by_form(form: dict) -> Query:
    queryset = Project.query
    if form.get('user_id'):
        queryset = queryset.join(ProjectUser, ProjectUser.project_id == Project.id). \
            filter(ProjectUser.user_id == form['user_id'])
    if form.get('status'):
        queryset = queryset.filter(Project.status == form['status'])
    return queryset


def generate_project_excel(projects: List[Project]):
    wb = Workbook()
    ws = wb.create_sheet('用户清单', 0)
    ws.append(('项目名', '背景', '开始时间', '结束时间', '进度更新时间', '状态', '技能'))
    for project in projects:
        row = [
            project.name or '',
            project.backend or '',
            to_formatted_time(project.start_at),
            to_formatted_time(project.end_at) if project.end_at else '',
            to_formatted_time(project.update_at),
            PROJECT_STATUS_MAP.get(project.status, ''),
            '，'.join([SKILL_MAP.get(i, '') for i in project.need_skills or []])
        ]
        ws.append(row)
    return wb
